package org.afk.sisy.db;

import java.nio.file.*;
import java.sql.*;
import java.util.*;

import org.afk.sisy.*;

public class JobDataBase
{
	private static final String JOBS = "jobs";
	private static final String PRIMARY_DIR = "primary_Dir";
	private static final String SECONDARY_DIR = "secondary_Dir";
	private static final String ATTIC_DIR = "attic_Dir";
	private static final String HASH_ALGORYTHM = "hash_algorythm";
	private static final String DB = "db";
	private static final String SCAN = "scan_Secondary";
	private static final String UNMATCHED_PRIMARY_OPTION = "unmatched_Primary_Option";
	private static final String UNMATCHED_SECONDARY_OPTION = "unmatched_Secondary_Option";
	private static final String MATCHOPTION = "match_Option";
	private static final String COMPARE_OPTION = "compare_Option";
	private static final String SYNC_OPTION = "sync_Option";
	private static final String ATTIC_OPTION = "attic_Option";

	private Connection conn;

	public JobDataBase()
	{
		super();
		try
		{
			Class.forName("org.h2.Driver");
		}
		catch (final ClassNotFoundException e)
		{
			throw new RuntimeException("No Database driver found. Please make sure a h2-x.x.xxx.jar is in the classpath");
		}
	}

	private Statement getStatement() throws SQLException
	{
		return conn.createStatement();
	}

	public List<SisyJob> getJobs() throws SQLException
	{
		final PreparedStatement selectDeletedStatement = prepareSelectAllJobsStatement();
		final ResultSet resultSet = selectDeletedStatement.getResultSet();
		return asSisyJobList(resultSet);
	}

	private List<SisyJob> asSisyJobList(final ResultSet resultSet) throws SQLException
	{
		if (resultSet == null)
			return Collections.emptyList();
		final List<SisyJob> result = new ArrayList<SisyJob>();

		for (;;)
		{
			if (!resultSet.next())
				break;
			final SisyJob job = createSisyJobFromResult(resultSet);
			result.add(job);

		}
		return result;
	}

	private SisyJob createSisyJobFromResult(final ResultSet resultSet) throws SQLException
	{
		final SisyJob sisyJob = new SisyJob();
		updateDataBaseFromResultSet(resultSet, sisyJob);

		updatePrimaryDirFromResultSet(resultSet, sisyJob);
		updateUnmatchedPrimaryFromResultSet(resultSet, sisyJob);

		sisyJob.setScanSecondaryDir(resultSet.getBoolean(SCAN));
		updateSecondaryDirFromResultSet(resultSet, sisyJob);
		updateUnmatchedSecondaryFromResultSet(resultSet, sisyJob);

		updateMatchFromResultSet(resultSet, sisyJob);

		updateCompareFromResultSet(resultSet, sisyJob);
		updateHashFromResultSet(resultSet, sisyJob);

		updateSyncFromResultSet(resultSet, sisyJob);

		updateAtticFromResultSet(resultSet, sisyJob);
		updateAtticDirFromResultSet(resultSet, sisyJob);

		return sisyJob;
	}

	private void updateDataBaseFromResultSet(final ResultSet resultSet, final SisyJob sisyJob) throws SQLException
	{
		final String sb = resultSet.getString(DB);
		if (sb != null)
			sisyJob.setDataBase(Paths.get(sb));
	}

	private void updateAtticDirFromResultSet(final ResultSet resultSet, final SisyJob sisyJob) throws SQLException
	{
		final String atticDir = resultSet.getString(ATTIC_DIR);
		if (atticDir != null)
			sisyJob.setAtticDir(Paths.get(atticDir));
	}

	private void updatePrimaryDirFromResultSet(final ResultSet resultSet, final SisyJob sisyJob) throws SQLException
	{
		final String primaryDir = resultSet.getString(PRIMARY_DIR);
		if (primaryDir != null)
			sisyJob.setPrimaryDir(Paths.get(primaryDir));
	}

	private void updateSecondaryDirFromResultSet(final ResultSet resultSet, final SisyJob sisyJob) throws SQLException
	{
		final String secondaryDir = resultSet.getString(SECONDARY_DIR);
		if (secondaryDir != null)
			sisyJob.setSecondaryDir(Paths.get(secondaryDir));
	}

	private void updateCompareFromResultSet(final ResultSet resultSet, final SisyJob sisyJob) throws SQLException
	{
		final String compareOption = resultSet.getString(COMPARE_OPTION);
		if (compareOption != null)
			sisyJob.setCompareOption(CompareOption.valueOf(compareOption));
	}

	private void updateSyncFromResultSet(final ResultSet resultSet, final SisyJob sisyJob) throws SQLException
	{
		final String syncOption = resultSet.getString(SYNC_OPTION);
		if (syncOption != null)
			sisyJob.setSyncOption(SyncOption.valueOf(syncOption));
	}

	private void updateAtticFromResultSet(final ResultSet resultSet, final SisyJob sisyJob) throws SQLException
	{
		final String atticOption = resultSet.getString(ATTIC_OPTION);
		if (atticOption != null)
			sisyJob.setAtticOption(AtticOption.valueOf(atticOption));
	}

	private void updateMatchFromResultSet(final ResultSet resultSet, final SisyJob sisyJob) throws SQLException
	{
		final String matchOption = resultSet.getString(MATCHOPTION);
		if (matchOption != null)
			sisyJob.setMatchOption(MatchOption.valueOf(matchOption));
	}

	private void updateUnmatchedSecondaryFromResultSet(final ResultSet resultSet, final SisyJob sisyJob) throws SQLException
	{
		final String unmatchedSecondaryOption = resultSet.getString(UNMATCHED_SECONDARY_OPTION);
		if (unmatchedSecondaryOption != null)
			sisyJob.setUnmatchedSecondaryOption(UnmatchedOption.valueOf(unmatchedSecondaryOption));
	}

	private void updateHashFromResultSet(final ResultSet resultSet, final SisyJob sisyJob) throws SQLException
	{
		final String hash = resultSet.getString(HASH_ALGORYTHM);
		if (hash != null)
			sisyJob.setAlgorythm(hash);
	}

	private void updateUnmatchedPrimaryFromResultSet(final ResultSet resultSet, final SisyJob sisyJob) throws SQLException
	{
		final String unmatchedPrimaryOption = resultSet.getString(UNMATCHED_PRIMARY_OPTION);
		if (unmatchedPrimaryOption != null)
			sisyJob.setUnmatchedPrimaryOption(UnmatchedOption.valueOf(unmatchedPrimaryOption));
	}

	private PreparedStatement prepareSelectAllJobsStatement() throws SQLException
	{
		return conn.prepareStatement("SELECT * FROM " + JOBS);
	}

	public void close() throws SQLException
	{
		conn.close();
	}

	public void open() throws SQLException
	{
		conn = DriverManager.getConnection("jdbc:h2:./sisy", "", "");
		prepareTable();

	}

	private void prepareTable() throws SQLException
	{
		getStatement().execute(createPrepareTableStatement());
		//		getStatement().execute("ALTER TABLE " + JOBS + " ADD IF NOT EXISTS " + NEW_COLUMN + " BOOL");
	}

	private String createPrepareTableStatement()
	{
		final StringBuilder builder = new StringBuilder("CREATE TABLE IF NOT EXISTS ");
		builder.append(JOBS).append(" (");
		builder.append(DB).append(" VARCHAR(1024) NOT NULL PRIMARY KEY, ");
		builder.append(PRIMARY_DIR).append(" VARCHAR(1024), ");
		builder.append(SECONDARY_DIR).append(" VARCHAR(1024), ");
		builder.append(SCAN).append(" BOOL, ");
		builder.append(UNMATCHED_PRIMARY_OPTION).append(" VARCHAR(20), ");
		builder.append(UNMATCHED_SECONDARY_OPTION).append(" VARCHAR(20), ");
		builder.append(MATCHOPTION).append(" VARCHAR(20), ");
		builder.append(COMPARE_OPTION).append(" VARCHAR(20), ");
		builder.append(HASH_ALGORYTHM).append(" VARCHAR(20), ");
		builder.append(SYNC_OPTION).append(" VARCHAR(20), ");
		builder.append(ATTIC_OPTION).append(" VARCHAR(20), ");
		builder.append(ATTIC_DIR).append(" VARCHAR(1024) ");
		builder.append(")");
		return builder.toString();
	}

	private PreparedStatement prepareInsertJobStatement() throws SQLException
	{
		final StringBuilder builder = new StringBuilder("INSERT INTO ");
		builder.append(JOBS).append(" (");

		builder.append(DB).append(", ");
		builder.append(PRIMARY_DIR).append(", ");
		builder.append(SECONDARY_DIR).append(", ");

		builder.append(SCAN).append(", ");
		builder.append(UNMATCHED_PRIMARY_OPTION).append(", ");
		builder.append(UNMATCHED_SECONDARY_OPTION).append(", ");

		builder.append(MATCHOPTION).append(", ");
		builder.append(HASH_ALGORYTHM).append(", ");
		builder.append(COMPARE_OPTION).append(", ");

		builder.append(SYNC_OPTION).append(", ");
		builder.append(ATTIC_OPTION).append(", ");
		builder.append(ATTIC_DIR).append(", ");

		builder.append(") VALUES(?,?,?,?,?,?,?,?,?,?,?)");
		return conn.prepareStatement(builder.toString());

	}

	public void addJob(final SisyJob sisyJob) throws SQLException
	{
		final PreparedStatement prepareInsertJobStatement = prepareInsertJobStatement();
		prepareInsertJobStatement.setString(1, sisyJob.getDataBase() != null ? sisyJob.getDataBase().toString() : null);
		prepareInsertJobStatement.setString(2, sisyJob.getPrimaryDir() != null ? sisyJob.getPrimaryDir().toString() : null);
		prepareInsertJobStatement.setString(3, sisyJob.getSecondaryDir() != null ? sisyJob.getSecondaryDir().toString() : null);

		prepareInsertJobStatement.setBoolean(4, sisyJob.isScanSecondaryDir());
		prepareInsertJobStatement.setString(5, String.valueOf(sisyJob.getUnmatchedPrimaryOption()));
		prepareInsertJobStatement.setString(6, String.valueOf(sisyJob.getUnmatchedSecondaryOption()));

		prepareInsertJobStatement.setString(7, String.valueOf(sisyJob.getMatchOption()));
		prepareInsertJobStatement.setString(8, sisyJob.getAlgorythm());
		prepareInsertJobStatement.setString(9, String.valueOf(sisyJob.getCompareOption()));

		prepareInsertJobStatement.setString(10, String.valueOf(sisyJob.getSyncOption()));
		prepareInsertJobStatement.setString(11, String.valueOf(sisyJob.getAtticOption()));
		prepareInsertJobStatement.setString(12, String.valueOf(sisyJob.getAtticDir()));
	}

}
